Attach Adapter
ATTACH { INPUT | OUTPUT } ADAPTER name TYPE type
TO STREAM stream [PROPERTIES { prop = value } [, ...] ] ;
Create Function
CREATE FUNCTION fname ( [ pname type [, ...] ] ) RETURNS type
{ statement [, ...] } END FUNCTION ;
statement
{ create_variable_statement | assignment_statement |
return_statement | break_statement | continue_statement } |
{ if_statement | case_statement | while_statement }
Create Parameter
CREATE PARAMETER data_type name [= value] ;
Create Schema
CREATE SCHEMA name { (col_name type [, ...]) |
INHERITS [FROM] schema_name [, ...]
[ (col_name type [, ...]) ] } ;
Create Stream
CREATE [ INPUT | OUTPUT | LOCAL ] STREAM name [schema_clause]
[PROPERTIES prop_def [, ...] ] ;
prop_def
{ GUARANTEED DELIVERY = {INHERIT | ENABLE | DISABLE} } |
{ GUARANTEED DELIVERY MAXIMUM QUEUE SIZE = size } |
{ GUARANTEED DELIVERY MAXIMUM AGE = age } |
{ MAXIMUM DELAY = max_delay } |
{ OUT OF ORDER DELAY = delay } |
Coral8 CCL Reference
282
{ SYNCHRONIZATION = { INHERIT | IN ORDER | OUT OF ORDER |
USE SERVER TIMESTAMP } } |
{ FILTERCOLUMNS = " col_name [, ...] " }
Create Variable
CREATE VARIABLE data_type name [= value] ;
Create Window
CREATE [ PUBLIC | MASTER ] WINDOW win_name schema_clause
{ keep_clause [, keep_clause] } | { MIRROR master_window }
[INSERT REMOVED [ROWS] INTO name ] [ properties_clause ]
properties_clause
PROPERTIES [ INDEXCOLUMNS="col_name [, ...] " ]
[ FILTERCOLUMNS="col_name [, ...] " |
[ FILTER=" value [, ...] " ]
[ FILTEREXPR=" expression " ] ]
Database
exec_clause select_clause from_clause [matching_clause]
[on_clause] [where_clause] [group_by_clause]
[having_clause] [order_by_clause] [limit_clause]
[output_clause] ;
Delete
on_clause [ when_clause ] DELETE FROM window_name [ where_clause
] ;
Import
IMPORT file ;
Insert Values
insert_clause values_clause output_clause ;
Query
Quick References
283
insert_clause select_clause from_clause [matching_clause]
[on_clause] [where_clause] [group_by_clause]
[having_clause] [order_by_clause] [limit_clause]
[output_clause] ;
Remote Procedure
exec_rem_proc_clause select_clause from_clause [matching_clause]
[on_clause] [where_clause] [group_by_clause]
[having_clause] [order_by_clause] [limit_clause]
[output_clause] ;
Set Variable
on_clause [when_clause] set_clause ;
Update Window
on_clause [when_clause] update_clause set_clause
[where_clause] [otherwise_insert_clause] ;
CCL Clauses Syntax Summary
Cache
CACHE { { CLEAR [ALL] ON source } |
{ MAXIMUM AGE age} |
{ MAXIMUM { MEMORY USAGE | SIZE } limit }
} [, ...]
Execute Remote Procedure
EXECUTE REMOTE PROCEDURE "service"
Execute Statement Database
EXECUTE STATEMENT DATABASE "service" [[statements]]
From: Comma-Separated Syntax
FROM {
stream [ [AS] alias] |
stream [ [AS] alias] keep_clause [keep_clause] |
Coral8 CCL Reference
284
window_name [ [AS] alias] |
( nested_join [on_clause] ) |
xmltable_exp |
subquery
} [, ...]
xmltable_exp
{ name [AS] alias } |
{ stream [AS] alias keep_clause [keep_clause] }
xmltable_func
xmltable_func
XMLTABLE ( column ROWS xpath
COLUMNS { expression AS out_column } [, ...] )
From: Database and Remote Subquery Syntax
FROM { { db_sub | rem_sub } { , | [LEFT OUTER] JOIN }
stream [[AS] alias ] } |
{ stream [[AS] alias ] { , | [RIGHT OUTER] JOIN}
{ db_sub | rem_sub } }
db_sub
( DATABASE "service" schema_clause [[statements]]
[cache_clause] ) [AS] alias
rem_sub
( REMOTE QUERY "service" schema_clause
( [ {value [AS] param} [, ...] ] )
[cache_clause] ) [AS] alias
From: Join Syntax
FROM {
stream [ [AS] alias] |
stream [ [AS] alias] keep_clause [keep_clause] |
window_name [ [AS] alias] |
nested_join |
xmltable_exp |
Quick References
285
subquery
} [ RIGHT | LEFT | FULL ] [OUTER] JOIN
{
stream [ [AS] alias] |
stream [ [AS] alias] keep_clause [keep_clause] |
window_name [ [AS] alias] |
nested_join |
xmltable_exp |
subquery
}
nested_join
FROM {
stream [ [AS] alias] |
stream [ [AS] alias] keep_clause [keep_clause] |
window_name [ [AS] alias] |
nested_join |
xmltable_exp |
subquery
} [ RIGHT | LEFT | FULL ] [OUTER] JOIN
{
stream [ [AS] alias] |
stream [ [AS] alias] keep_clause [keep_clause] |
window_name [ [AS] alias] |
nested_join |
xmltable_exp |
subquery
} [on_clause]
subquery
( select_clause from_clause [matching_clause]
[on_clause] [where_clause] [group_by_clause]
[having_clause] [order_by_clause] [output_clause] )
[AS] alias [keep_clause] [keep_clause]
Group By
GROUP BY { column | gettimestamp } [, ...]
Having
Coral8 CCL Reference
286
HAVING boolean
Insert Into
INSERT INTO name [ (column [, ...]) ]
Insert When
INSERT
{ WHEN condition THEN name
[ (column [, ...]) ] } [, ...]
[ ELSE name [ (column [, ...]) ] ]
Keep
time_policy | count_policy
time_policy
KEEP { { [EVERY] interval [OFFSETtimes_list BY interval] } |
FOR interval_col | UNTIL times_list }
[ PER column [, ...] | UNGROUPED ]
times_list
time_spec | (time_spec [, ...])
time_spec
' [ SUN | MON | TUE | WED | THU | FRI | SAT ]
hour : minute [ : second [. fraction ]] [timezone]'
count_policy
KEEP { [EVERY] count BUCKETS { BY column } [...] } |
{
{ [EVERY] count ROW[S] } |
{ LAST [ROW] } |
{ count { LARGEST | SMALLEST } [DISTINCT] ROW[S]
{ BY column } [...] } |
{ ALL [ROW[S]] }
[ { PER column } [...] | UNGROUPED ]
}
Quick References
287
Limit
LIMIT count [ROW[S]] [ OFFSET skip [ROW[S]] ]
[ PER column [ ... ] ]
Matching
MATCHING [ ONCE [ ( event [, ...] ) ]
[ interval : pattern ] [ on_clause ]
pattern
[!] { event | ( pattern ) | [ interval : pattern ] }
[ {&& | || | ,}
[!] { event | ( pattern ) | [ interval : pattern ] } ]
[...]
On: Trigger Syntax
ON name [ [AS] alias ]
On: Join Syntax
ON inner_condition |
source.column = source.column [AND ...]
On: Pattern Matching Syntax
ON {source.column = source.column [= ...] } [AND ...]
Order By
ORDER BY column [ ASC[ENDING] | DESC[ENDING] ] [, ...]
Otherwise Insert
OTHERWISE INSERT value [AS column] [, ...]
Output
OUTPUT { [ALL] EVERY { count ROW[S] |
interval [OFFSET BY interval] } } |
{ {AFTER | FIRST WITHIN} {count ROW[S] | interval} } |
Coral8 CCL Reference
288
{ [ALL] AT times_list }
[ UNGROUPED | PER column [...] ]
Schema
SCHEMA name |
'file' |
( column type [, ...] )
Select
SELECT { expression [AS column | alias | parameter ] } [, ...]
Set: Set Variable Statement Syntax
SET name = value [, ...]
Set: Window Syntax
SET { value AS column [, ...] } |
{ column = value [, ...] }
Update
UPDATE window[ [AS] alias ]
Values
VALUES ( column_expression [, ...] ) [, ...]
When
WHEN trigger
Where
WHERE condition
Operators and Operand Data Types
Operator Result Operands
+, - (as unary operators) Integer Integer
Quick References
289
+, - (as unary operators) Long Long
+, - (as unary operators) Float Float
+, - (as unary operators) Interval Interval
+,-,*, /, mod Integer Integer, Integer
+,-,*, /, mod Long Long, Long
+,-,*, /, ^, mod Float Float, Float
+ BLOB BLOB
^ Float Integer, Float
^ Float Long, Float
+,-,mod Interval Interval, Interval
/ Float Interval, Interval
+ Timestamp Interval, Timestamp
*,/ Interval Interval, Integer
*,/ Interval Interval, Long
*,/ Interval Interval, Float
- Interval Timestamp, Timestamp
+,- Timestamp Timestamp, Interval
=, !=, <>, <, >, <=, >= Boolean Boolean, Boolean
=, !=, <>, <, >, <=, >= Boolean Integer, Integer
=, !=, <>, <, >, <=, >= Boolean Long, Long
=, !=, <>, <, >, <=, >= Boolean Float, Float
=, !=, <>, <, >, <=, >= Boolean Interval, Interval
=, !=, <>, <, >, <=, >= Boolean String, String
=, !=, <>, <, >, <=, >= Boolean Timestamp, Timestamp
=, !=, <>, <, >, <=, >= Boolean BLOB
NOT Boolean Boolean
AND, OR, XOR Boolean Boolean, Boolean
IN Boolean Boolean, Boolean,
IN Boolean Float, Float,
IN Boolean Integer, Integer,
IN Boolean Long, Long,
Coral8 CCL Reference
290
IN Boolean Interval, Interval,
IN Boolean String, String,
IN Boolean Timestamp, Timestamp,
||, + String String, String
[ ]. (previous operator) Boolean Integer, Boolean
[ ]. (previous operator) Float Integer, Float
[ ]. (previous operator) Integer Integer, Integer
[ ]. (previous operator) Long Integer, Long
[ ]. (previous operator) String Integer, String
[ ]. (previous operator) Interval Integer, Interval
[ ]. (previous operator) Timestamp Integer, Timestamp
[ ]. (previous operator) BLOB Integer, BLOB
[ ]. (previous operator) XML Integer, XML
LIKE Boolean String, String
REGEXP_LIKE Boolean String, String